package com.silverduner.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author Yuwen Sun
 * 
 */
public class DbUtils { 
	private static final Log LOGGER = LogFactory.getLog(DbUtils.class);
	private static final String LINESEPERATOR = System
			.getProperty("line.separator");

	public static final String DRIVER_JDBC_ORACLE = "oracle.jdbc.driver.OracleDriver";
	public static final String DRIVER_JDBC_MYSQL = "com.mysql.jdbc.Driver";
	public static final String DRIVER_JDBC_MSSQL = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

	public static Connection createConnection(final String driver, final String url,
			final String user, final String password) throws ClassNotFoundException,
			SQLException {
		Class.forName(driver);
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * quietly close statement
	 * 
	 * @param statement
	 */
	public static void close(final Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				LOGGER.warn(e.getMessage());
			}
		}
	}

	/**
	 * quietly close result set
	 * 
	 * @param rs
	 */
	public static void close(final ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				LOGGER.warn(e.getMessage());
			}
		}
	}

	/**
	 * quietly close connection
	 * 
	 * @param con
	 */
	public static void close(final Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				LOGGER.warn(e.getMessage());
			}
		}
	}

    /**
     * Code origin: Apache Commons Dbutils
     */
	public static void fillParameters(final PreparedStatement stmt, final Object[] params)
			throws SQLException {

		if (params == null) {
			return;
		}

		ParameterMetaData pmd = stmt.getParameterMetaData();
		if (pmd.getParameterCount() < params.length) {
			throw new SQLException("Too many parameters: expected "
					+ pmd.getParameterCount() + ", was given " + params.length);
		}
		for (int i = 0; i < params.length; i++) {
			if(params[i] != null && params[i] instanceof java.util.Date){
				java.sql.Date date = new java.sql.Date(((java.util.Date)params[i]).getTime());
				stmt.setDate(i + 1, date);
			}
			else if (params[i] != null) { 
				stmt.setObject(i + 1, params[i]);
			} else {
				// VARCHAR works with many drivers regardless
				// of the actual column type. Oddly, NULL and
				// OTHER don't work with Oracle's drivers.
				final int sqlType = Types.VARCHAR;
				stmt.setNull(i + 1, sqlType);
			}
		}
	}

	/**
	 * @param rs
	 * @param file
	 * @throws IOException
	 * @throws SQLException
	 * @deprecated development only. it's not a good implementation
	 */
	@Deprecated
	public static void toCsv(final ResultSet rs, final File file) throws IOException,
			SQLException {
		FileWriter fw = null;

		try {
			fw = new FileWriter(file);
			final int length = rs.getMetaData().getColumnCount();
			for (int i = 1; i <= length; i++) {
				fw.write(rs.getMetaData().getColumnName(i) + ",");
			}
			fw.write(LINESEPERATOR);
			while (rs.next()) {
				for (int i = 1; i <= length; i++) {
					fw.write(rs.getString(i) + ",");
				}
				fw.write(LINESEPERATOR);
			}
			fw.flush();

		} catch (IOException e) {
			LOGGER.error(e.getMessage(), e);
			throw e;
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
			throw e;
		} finally {
			try {
				if (fw != null)
					fw.close();
			} catch (IOException e) {
				LOGGER.error(e.getMessage(), e);
			}
		}
	}
}
